let db = require('../db.js');

//创建管理员表
exports.createdAdmin = async function () {
    let sql = `
    DROP TABLE IF EXISTS admin;
    CREATE TABLE admin (
        id int(11) unsigned NOT NULL AUTO_INCREMENT,
        userName  varchar(20) NOT NULL COMMENT '管理员昵称',
        password  varchar(200) NOT NULL COMMENT '密码',
        email   varchar(50) DEFAULT NULL COMMENT '邮箱',
        role    varchar(20) NOT NULL COMMENT '角色',
         avatar  varchar(200) DEFAULT '/images/avatar.gif' COMMENT '头像',
         token  varchar(500) DEFAULT NULL COMMENT '登录token',
         isdelete  enum('y','n') DEFAULT 'n',
         createdAt  timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        PRIMARY KEY ( id )
      ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; 
    `
    let data = await db.query(sql);
    return data[0];
}

//创建数据库备份表
exports.createdBackUpSql = async function () {
    let sql = `
    DROP TABLE IF EXISTS backup_sql;
    CREATE TABLE backup_sql (
        id int(11) unsigned NOT NULL AUTO_INCREMENT,
        backupStatus tinyint(1) DEFAULT NULL COMMENT '自动备份状态',
        url varchar(50) DEFAULT NULL COMMENT '数据库备份下载地址',
        createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP,
        updatedAt timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
      ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
       
    `
    let data = await db.query(sql);
    return data[0];
}
//创建公司信息表

exports.createCompany = async function () {
    let sql = `
    DROP TABLE IF EXISTS company;
    CREATE TABLE company (
      id int(11) unsigned NOT NULL AUTO_INCREMENT,
      name varchar(50) DEFAULT NULL COMMENT '公司名称',
      address varchar(200) DEFAULT NULL COMMENT '公司地址',
      createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      updatedAt timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
    
       
    `
    let data = await db.query(sql);
    return data[0];
}

//创建部门信息表
exports.createDepartment = async function () {
    let sql = `
    DROP TABLE IF EXISTS department;

    CREATE TABLE department (
      id int(11) unsigned NOT NULL AUTO_INCREMENT,
      name varchar(50) DEFAULT NULL COMMENT '部门名称',
      director varchar(20) DEFAULT NULL COMMENT '部门负责人',
      phone varchar(50) DEFAULT NULL COMMENT '负责人联系电话',
      createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      updatedAt timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
    
       
    `
    let data = await db.query(sql);
    return data[0];
}

//创建员工信息表

exports.createEmployees = async function () {
    let sql = `
    DROP TABLE IF EXISTS employees;

    CREATE TABLE employees (
      id int(11) unsigned NOT NULL AUTO_INCREMENT,
      num varchar(20) DEFAULT NULL COMMENT '员工工号',
      name varchar(50) DEFAULT NULL COMMENT '员工姓名',
      departmentId int(11) DEFAULT NULL COMMENT '所属部门',
      departmentName varchar(50) DEFAULT NULL COMMENT '部门名称',
      phone varchar(50) DEFAULT NULL COMMENT '联系方式',
      url varchar(200) DEFAULT NULL COMMENT '图片地址',
      isdelete enum('y','n') DEFAULT 'n' COMMENT '是否删除',
      synchronous enum('y','n') DEFAULT NULL,
      createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      updatedAt timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
       
    `
    let data = await db.query(sql);
    return data[0];
}

//创建设备表
exports.createEquipment = async function () {
    let sql = `
    DROP TABLE IF EXISTS equipment;

    CREATE TABLE equipment (
      id int(11) unsigned NOT NULL AUTO_INCREMENT,
      mac varchar(100) NOT NULL COMMENT '设备mac地址',
      isonline enum('y','n') DEFAULT NULL COMMENT '设备在线状态',
      createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      updatedAt timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 
    `
    let data = await db.query(sql);
    return data[0];
}

//创建登录token 表

exports.createLoginToken = async function (req, res) {
    let sql = `
    DROP TABLE IF EXISTS login_token;

    CREATE TABLE login_token (
      id int(11) unsigned NOT NULL AUTO_INCREMENT,
      token varchar(200) DEFAULT NULL COMMENT '登录界面token',
      createdAt timestamp NULL DEFAULT NULL,
      updatedAt timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=589 DEFAULT CHARSET=utf8;
    
    `
    let data = await db.query(sql);
    return data[0];
}

//创建消息通知表
exports.createNotice = async function () {
    let sql = `
 
DROP TABLE IF EXISTS notice;

CREATE TABLE notice (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  title varchar(50) DEFAULT NULL COMMENT '通知的标题',
  content varchar(200) DEFAULT NULL COMMENT '通知的内容',
  img varchar(200) DEFAULT NULL COMMENT '通知的图片',
  noticeType enum('文字','图') DEFAULT NULL,
  isdelete enum('y','n') DEFAULT 'n' COMMENT '是否删除（y代表已删除，n代表未删除）',
  createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updatedAt timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

    `
    let data = await db.query(sql);
    return data[0];
}

//创建操作日志表
exports.createOperationLog = async function () {
    let sql = `
    DROP TABLE IF EXISTS operation_log;

    CREATE TABLE operation_log (
      id int(11) unsigned NOT NULL AUTO_INCREMENT,
      adminId int(11) DEFAULT NULL COMMENT '管理员Id',
      adminName varchar(50) DEFAULT NULL COMMENT '管理员名称',
      role varchar(20) DEFAULT NULL COMMENT '角色',
      action varchar(50) DEFAULT NULL COMMENT '操作动作',
      content varchar(200) DEFAULT NULL COMMENT '操作内容',
      createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      updatedAt timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=utf8;
    
        `
    let data = await db.query(sql);
    return data[0];
}

//创建版本表

exports.createVersion = async function () {
    let sql = `
    DROP TABLE IF EXISTS version;

    CREATE TABLE version (
      id int(11) unsigned NOT NULL AUTO_INCREMENT,
      num float DEFAULT NULL COMMENT '版本编号',
      content varchar(200) DEFAULT NULL COMMENT '更新内容',
      url varchar(200) DEFAULT NULL COMMENT '下载地址',
      isdelete enum('y','n') DEFAULT 'n' COMMENT '是否删除',
      createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      updateAt timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
    
        `
    let data = await db.query(sql);
    return data[0];
}

//插入数据备份设置数据

exports.insertBackUp = async function () {
    let sql = `
    LOCK TABLES backup_sql WRITE;

insert  into backup_sql(id,backupStatus,url,createdAt,updatedAt) values (2,1,'/backup.sql','2018-08-27 16:29:07','2018-08-27 17:24:31');

UNLOCK TABLES;

    `
    let data = await db.query(sql);
    return data[0];
}
//生成管理员信息
exports.createAdminInfo = async function (userName, passwordMD5) {
    let sql = `
    LOCK TABLES admin WRITE;

      insert  into admin(userName,password,role) 
      values (:userName,:passwordMD5,'admin');

    UNLOCK TABLES;
    
    `
    let data = await db.query(sql,{userName, passwordMD5});
    return data[0];
}

//生成公司信息
exports.createCompanyInfo  = async  function(companyName){
    let sql=`
    LOCK TABLES company WRITE;

insert  into company(name) 
values (:companyName);

UNLOCK TABLES;
   ` 
      let data = await db.query(sql,{companyName});
   return data[0];
}